library(dplyr)

df<-read.csv("/Users/gnovoa/Library/Mobile Documents/com~apple~CloudDocs/Documents/Coloring in the Lines/RR/full_data_for_models.csv")
df$city_pop1m<-df$city_pop/1e6

# Bring in JdBK data 
setwd("/Users/gnovoa/Downloads")
jdbk <- read.csv('ledb_candidatelevel.csv') %>%
  subset(year < 2011 & year > 2005 & office_consolidated == "Mayor" & winner == 'win') %>%
  mutate(
    city = gsub('\\.', '', geo_name),
    state = state_abb
  ) %>%
  group_by(city, state) %>%
  filter(year == max(year)) %>%
  ungroup()

df2<-left_join(df, jdbk, by=c('city', 'state')) 


df2$diff_black <- df2$actual_maj_blk_c - df2$majority_black_c_median
df2$diff_hisp <- df2$actual_maj_hisp_c - df2$majority_hisp_c_median
df2$diff_black_absolute <- round(df2$actual_maj_blk_c*df2$ndists - df2$majority_black_c_median*df2$ndists)
df2$diff_hisp_absolute <- round(df2$actual_maj_hisp_c*df2$ndists - df2$majority_hisp_c_median*df2$ndists)
df2$diff_combined<-df2$actual_maj_blk_c + df2$actual_maj_hisp_c - df2$majority_black_c_median +df2$majority_hisp_c_median
df2$hisp_mayor<-ifelse(df2$mayor_race=='hispanic',1,0)

write.csv(df2, file= 'full_data_for_models_wdbk.csv')
